package com.fare.taluo.queue;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Border;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.lang3.StringUtils;

import com.dayatang.domain.Entity;
import com.dayatang.domain.InstanceFactory;
import com.fare.taluo.application.plane.agent.AgentApplication;
import com.fare.taluo.application.report.csair.CsairTaobaoApplication;
import com.fare.taluo.application.system.email.EmailSendApplication;
import com.fare.taluo.application.system.email.ReportEmailApplication;
import com.fare.taluo.core.domain.BaseEntity;
import com.fare.taluo.core.domain.queue.message.QueueEmailMessageEntity;
import com.fare.taluo.util.DateUtils;
import com.fare.taluo.util.StringAndDateAndFileUtil;
import com.fare.taluo.vo.core.AgentEntityVO;
import com.fare.taluo.vo.report.CsairTaobaoEntityVO;
import com.fare.taluo.vo.reportemail.EmailVO;
import com.fare.util.ReportProperties;

public class ExportCsairTaobaoQuartzJob extends BaseEntity<ExportCsairTaobaoQuartzJob> implements Entity{
	/**
	 * 
	 */
	private static final long serialVersionUID = 3212637296384635734L;
	@Resource(name ="reportEmailApplication")
	private ReportEmailApplication reportEmailApplication;
	private String email = null;
	private String currentDate = DateUtils.getDateStr("yyyy-MM-dd");
	private Map<String, CsairTaobaoEntityVO> reportMap;
	private List<Map<String, Map<String, CsairTaobaoEntityVO>>> reportList;
	@Resource(name = "csairTaobaoApplication")
	private CsairTaobaoApplication csairTaobaoApplication;
	@Resource(name = "agentApplication")
	private AgentApplication agentApplication;
	
	private jxl.write.WritableFont titleFont;
	private jxl.write.WritableCellFormat titleFormat;
	private jxl.write.WritableFont normalFont;
	private jxl.write.WritableFont boldFont;
	private jxl.write.WritableCellFormat normalFormat;
	private jxl.write.WritableCellFormat boldFormat;
	private static final int COLUMN_WIDTH = 13;
	private List<EmailVO> emailList;
	private String flightDate ;
	public void execute() throws Exception {
		try {
			flightDate = this.getFlightDateByWeekDay();
			
			
			// 获得数据
			//reportMap = this.getReportMap();
			List<Map<String, Map<String, CsairTaobaoEntityVO>>> reportList =  getReportList();
			// 生成路径
			String path = ReportProperties.getInstance().getSavePath()
					.endsWith("/") ? ReportProperties.getInstance()
					.getSavePath() : ReportProperties.getInstance()
					.getSavePath() + "/";

			String fileName = "南航淘宝价差监控报表_" + DateUtils.getDateStr("yyyy-MM-dd") + ".xls";
			// 生成excel
			
			//System.out.println("path:"+path);
			exportExcel(reportList, path, fileName);
			File file = new File(path + fileName);
			
			//获取后台要发送的Email
			if(email==null || email==""){
				emailList=reportEmailApplication.findAllEmail(1);//0表示不接受状态，1表示接受状态
				StringBuffer emailStr=new StringBuffer();
				for(EmailVO emailVo:emailList){
					emailStr.append(","+emailVo.getEmailAddress());
				}
				if(emailStr.length()>1 && emailStr !=null){
					email=emailStr.toString().substring(1);
				}
			}
			if (file.exists() && StringUtils.isNotBlank(getEmail())) {
				QueueEmailMessageEntity email = new QueueEmailMessageEntity();
				email.setAttachPath(file.getPath());// new
													// String(location.getBytes("UTF-8"),"ISO8859-1")
				email.setBody("<p>您好!<p><p>&nbsp;&nbsp;已生成您需要的南航淘宝价差监控报表，"
						+ "</p>" + "<p>请查收附件。</p>");
				email.setSubject("南航淘宝价差监控报表" + currentDate);
				email.setToAddress(getEmail());
				email.setName("南航淘宝价差监控报表");
				InstanceFactory.getInstance(EmailSendApplication.class).save(
						email);

			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}

	}
	private String getFlightDateByWeekDay(){
		//Calendar cal=Calendar.getInstance();
		
		 //if( cal.get(Calendar.DAY_OF_WEEK) == 5){
		//	 return DateUtils.getDateAfter(3);
		// }else{
			 return DateUtils.getDateAfter(2);
		// }
	}
	
	private List<Map<String, Map<String, CsairTaobaoEntityVO>>> getReportList(){
		
		List<Map<String, Map<String, CsairTaobaoEntityVO>>> list = new ArrayList<Map<String,Map<String,CsairTaobaoEntityVO>>>();
		List<String> initData = this.initData();
 		for (String data : initData) {
 			Map<String, Map<String, CsairTaobaoEntityVO>> temp = csairTaobaoApplication.findByConditionToMap(data.split("-")[0], data.split("-")[1], null,  flightDate,com.fare.util.DateUtils.getDateStr("yyyy-MM-dd"));
 			if(null != temp){
 				list.add(temp);
 			}
 			
		}
		return list;
	}
	
	private List<String> initData(){
		List<String> list = new ArrayList<String>();
		list.add("PEK-CAN");
		list.add("CAN-PEK");
		list.add("SYX-PEK");
		list.add("CTU-CAN");
		list.add("PEK-SZX");
		list.add("CTU-PEK");
		list.add("PEK-URC");
		list.add("CAN-SHA");
		list.add("CAN-HGH");
		list.add("CAN-URC");
		list.add("PEK-SYX");
		list.add("SZX-PEK");
		list.add("CAN-CTU");
		list.add("SHA-CAN");
		list.add("CTU-SZX");
		list.add("HGH-CAN");
		list.add("PEK-KWE");
		list.add("URC-PEK");
		list.add("LJG-KMG");
		list.add("SHA-URC");
		list.add("CAN-WNZ");
		list.add("SYX-PVG");
		list.add("CAN-CKG");
		list.add("CAN-HAK");
		list.add("CTU-CGO");
		list.add("KWE-CAN");
		list.add("HAK-CAN");
		list.add("CAN-SYX");
		list.add("CAN-KMG");
		list.add("CAN-YIW");
		list.add("HAK-PEK");
		list.add("CKG-CAN");
		list.add("PVG-SYX");
		list.add("SYX-CAN");
		list.add("URC-CAN");
		list.add("SHA-SZX");
		list.add("NKG-CAN");
		list.add("CAN-TSN");
		list.add("CTU-URC");
		list.add("KMG-CAN");
		list.add("SYX-HRB");
		list.add("SZX-CTU");
		list.add("CAN-DLC");
		list.add("HRB-CAN");
		list.add("PEK-NNG");
		list.add("SYX-CKG");
		list.add("SZX-SHA");
		list.add("DLC-PVG");
		list.add("PEK-HAK");
		list.add("CAN-NKG");
		list.add("KWE-PEK");
		list.add("CTU-WUH");
		list.add("YIW-CAN");
		list.add("SHE-CAN");
		list.add("URC-SHA");
		list.add("CKG-KMG");
		list.add("SHE-PVG");
		list.add("CAN-SHE");
		list.add("HGH-SZX");
		list.add("CGQ-CAN");
		list.add("PEK-DLC");
		list.add("XIY-URC");
		list.add("XMN-PEK");
		list.add("ZUH-PEK");
		list.add("CAN-TAO");
		list.add("PEK-CTU");
		list.add("CAN-CGO");
		list.add("SZX-HGH");
		list.add("CAN-XIY");
		list.add("TYN-CAN");
		list.add("WUH-KMG");
		list.add("CAN-HRB");
		list.add("PVG-DLC");
		list.add("CAN-KWE");
		list.add("SZX-KMG");
		list.add("TSN-CAN");
		list.add("PEK-HRB");
		list.add("CKG-HGH");
		list.add("DLC-CAN");
		list.add("XIY-CAN");
		list.add("DLC-PEK");
		list.add("CTU-SHE");
		list.add("CAN-CGQ");
		list.add("SWA-PEK");
		list.add("CSX-PEK");
		list.add("CGQ-PVG");
		list.add("WUH-URC");
		list.add("SHE-CTU");
		list.add("PEK-CGQ");
		list.add("HRB-PEK");
		list.add("CAN-TYN");
		list.add("PEK-SWA");
		list.add("PEK-ZUH");
		list.add("TAO-CAN");
		list.add("PEK-XMN");
		list.add("CGO-CTU");
		list.add("KMG-SZX");
		list.add("URC-WUH");
		list.add("WUH-CTU");
		list.add("CGQ-SZX");
		return list;
	}
	 
	
	private void exportExcel(List<Map<String, Map<String, CsairTaobaoEntityVO>>> reportList,
			String path, String fileName)
			throws Exception {
		WritableWorkbook wwb = null;
		OutputStream os = null;
		File file = null;
		try {
			// this.initDate();
			file = this.createFile(path, fileName);
			os = new FileOutputStream(file);
			wwb = Workbook.createWorkbook(os); // 建立excel文件
			// 定义样式
			jxl.write.WritableFont normalFont = new jxl.write.WritableFont(
					WritableFont.createFont("宋体"), 10);
			jxl.write.WritableCellFormat normalFormat = new jxl.write.WritableCellFormat(
					normalFont);
			normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
			normalFormat.setAlignment(Alignment.CENTRE);
			normalFormat.setWrap(false);
				WritableSheet ws = wwb.createSheet(com.fare.util.DateUtils.getDateStr("yyyy-MM-dd"), 0); // sheet名称
				ws = setHeadFirst(ws);
				ws = setDataFirst(ws, reportList);
			wwb.write();
		} finally {

			if (wwb != null)
				wwb.close();
			if (os != null)
				os.close();
			wwb = null;
			os = null;
			file = null;
		}
	}

	private WritableSheet setDataFirst(WritableSheet ws,
			List<Map<String, Map<String, CsairTaobaoEntityVO>>> reportList) throws Exception {
		this.formatWriteable();
		//TreeMap<String, CsairTaobaoEntityVO> treeMap = new TreeMap<String, CsairTaobaoEntityVO>(reportMap);

	//	Iterator lineKeys = treeMap.keySet().iterator();
		int i=1;
	//	int col = 1;
		DecimalFormat doubleFormat = new DecimalFormat();  
		doubleFormat.applyPattern("#0.00");
		for (int j = 0; j < reportList.size(); j++) {
			//航线， 渠道  vo
			Map<String, Map<String, CsairTaobaoEntityVO>> mapmap = reportList.get(j);
			if(null == mapmap || mapmap.size() == 0){
				continue;
			}
			String line = mapmap.keySet().iterator().next();
			CsairTaobaoEntityVO taobao = mapmap.get(line).get("00001");
			CsairTaobaoEntityVO qunaer = mapmap.get(line).get("00002");
			String flightNo = null;
			if(taobao != null || qunaer != null){
				flightNo = (taobao == null ? qunaer.getFlightNo() : taobao.getFlightNo());
			}else{
				flightNo = "";
			}
			
			
			
			// 对每个对象循环
			// 周期
			Label label0 = new Label(0, i, ""+i, normalFormat);
			ws.addCell(label0);
			// 航线
			Label label1 = new Label(1, i, line, normalFormat);
			ws.addCell(label1);
			// 航班日期
			Label label2 = new Label(2, i, flightDate,
					normalFormat);
			ws.addCell(label2);
			// 航班
			Label label3 = new Label(3, i,flightNo, normalFormat);
			ws.addCell(label3);
			// 航程类型
			Label label4 = new Label(4, i, "单程", normalFormat);
			ws.addCell(label4);
				//B2C价格
				Label label5 = new Label(5, i,""+( taobao==null?(qunaer==null?"0":qunaer.getPrice()):taobao.getPrice()), normalFormat);
				ws.addCell(label5);
				if(null != taobao){
					//代理价格
					Label label6 = new Label(6, i,""+taobao.getChannelPrice(), normalFormat);
					ws.addCell(label6);
					//销售平台
					Label label7 = new Label(7, i,""+taobao.getChannelName(), normalFormat);
					ws.addCell(label7);
					//代理的平台名称
					if(StringAndDateAndFileUtil.checkString(taobao.getAgentName()) && isNumeric(taobao.getAgentName())){
						AgentEntityVO taoAgent = agentApplication.findAgentEntityByAgentCode(taobao.getAgentName());
						Label label8 = new Label(8, i,taoAgent==null? taobao.getAgentName(): taoAgent.getName(), normalFormat);
						ws.addCell(label8);
					}else{
						Label label8 = new Label(8, i, taobao.getAgentName(), normalFormat);
						ws.addCell(label8);
					}
					
					//价差金额
					Label label9 = new Label(9, i,""+taobao.getSpreadAmount(), normalFormat);
					ws.addCell(label9);
					//价差
					Label label10 = new Label(10, i,""+doubleFormat.format(taobao.getSpread()*100)+"%", normalFormat);
					ws.addCell(label10);
					//价差原因
					Label label11 = new Label(11, i,"", normalFormat);
					ws.addCell(label11);
				}
				
				if(null != qunaer){
					//----
					//代理价格
					Label label12 = new Label(12, i,""+qunaer.getChannelPrice(), normalFormat);
					ws.addCell(label12);
					//销售平台
					Label label13 = new Label(13, i,""+qunaer.getChannelName(), normalFormat);
					ws.addCell(label13);
					//代理的平台名称
					Label label14 = new Label(14, i, qunaer.getAgentName(), normalFormat);
					ws.addCell(label14);
					//价差金额
					Label label15 = new Label(15, i,""+qunaer.getSpreadAmount(), normalFormat);
					ws.addCell(label15);
					//价差
					Label label16 = new Label(16, i,""+doubleFormat.format(qunaer.getSpread()*100)+"%", normalFormat);
					ws.addCell(label16);
					//价差原因
					Label label17 = new Label(17, i,"", normalFormat);
					ws.addCell(label17);
				}
				
			++i;
		}
		
		
		
		/*while (lineKeys.hasNext()) {
			String key = (String) lineKeys.next();// 航线
			CsairTaobaoEntityVO vo = treeMap.get(key);
			// 对每个对象循环
				// 周期
				Label label0 = new Label(0, i, "", normalFormat);
				ws.addCell(label0);
				// 航线
				Label label1 = new Label(1, i, vo.getFromCity() + "-"
						+ vo.getToCity(), normalFormat);
				ws.addCell(label1);
				// 管辖单位
				Label label2 = new Label(2, i, "",
						normalFormat);
				ws.addCell(label2);
				// 航班日期
				Label label3 = new Label(3, i,DateUtils.getDateAfter(1), normalFormat);
				ws.addCell(label3);
				// 航班
				Label label4 = new Label(4, i, vo.getFlightNo(), normalFormat);
				ws.addCell(label4);
					//航程类型
					Label label5 = new Label(5, i, "单程", normalFormat);
					ws.addCell(label5);
					//B2C价格
					Label label6 = new Label(6, i,""+vo.getPrice(), normalFormat);
					ws.addCell(label6);
					//代理价格
					Label label7 = new Label(7, i,""+vo.getChannelPrice(), normalFormat);
					ws.addCell(label7);
					//销售平台
					Label label8 = new Label(8, i, "淘宝旅行", normalFormat);
					ws.addCell(label8);
					//代理的平台名称
					Label label9 = new Label(9, i,vo.getAgentName(), normalFormat);
					ws.addCell(label9);
					//价差金额
					Label label10 = new Label(10, i,""+vo.getSpreadAmount(), normalFormat);
					ws.addCell(label10);
					//价差
					Label label11 = new Label(11, i,""+vo.getSpread(), normalFormat);
					ws.addCell(label11);
					//价差原因
					Label label12 = new Label(12, i,"", normalFormat);
					ws.addCell(label12);
				++i;
			}*/
		return ws;
	}
	public boolean isNumeric(String str){ 
		   Pattern pattern = Pattern.compile("[0-9]*"); 
		   Matcher isNum = pattern.matcher(str);
		   if( !isNum.matches() ){
		       return false; 
		   } 
		   return true; 
		}
	private File createFile(String filePath, String fileName)
			throws IOException {
		String excelpath = filePath;
		System.out.println("输出路径：" + excelpath);
		// 创建文件
		File path = new File(excelpath);
		if (path.exists()) {
			path.delete();
			path.createNewFile();
		} else {
			path.mkdirs();
		}
		File file = new File(filePath + File.separator + fileName);
		file.createNewFile();
		return file;

	}

	private WritableSheet setHeadFirst(WritableSheet ws) throws Exception {
		this.formatWriteable();
		int row = 0;
		Label label0 = new Label(0, row, "序号", titleFormat);
		ws.addCell(label0);
		ws.setColumnView(0, COLUMN_WIDTH);
		Label label1 = new Label(1, row, "航线", titleFormat);
		ws.addCell(label1);
		ws.setColumnView(1, COLUMN_WIDTH);
		Label label2 = new Label(2, row, "航班日期", titleFormat);
		ws.addCell(label2);
		ws.setColumnView(2, COLUMN_WIDTH);
		Label label3 = new Label(3, row, "航班", titleFormat);
		ws.addCell(label3);
		ws.setColumnView(3, COLUMN_WIDTH);
		Label label4 = new Label(4, row, "航程类型", titleFormat);
		ws.addCell(label4);
		ws.setColumnView(4, COLUMN_WIDTH);
		Label label5 = new Label(5, row, "B2C价格", titleFormat);
		ws.addCell(label5);
		ws.setColumnView(5, COLUMN_WIDTH);
		Label label6 = new Label(6, row, "代理价格", titleFormat);
		ws.addCell(label6);
		ws.setColumnView(6, COLUMN_WIDTH);
		Label label7 = new Label(7, row, "销售平台", titleFormat);
		ws.addCell(label7);
		ws.setColumnView(7, COLUMN_WIDTH);
		Label label8 = new Label(8, row, "代理的平台名称", titleFormat);
		ws.addCell(label8);
		ws.setColumnView(8, COLUMN_WIDTH);
		Label label9 = new Label(9, row, "价差金额", titleFormat);
		ws.addCell(label9);
		ws.setColumnView(9, COLUMN_WIDTH);
		Label label10 = new Label(10, row, "价差", titleFormat);
		ws.addCell(label10);
		ws.setColumnView(10, COLUMN_WIDTH);
		Label label11 = new Label(11, row, "价差原因", titleFormat);
		ws.addCell(label11);
		ws.setColumnView(11, COLUMN_WIDTH);
		Label label12 = new Label(12, row, "代理价格", titleFormat);
		ws.addCell(label12);
		ws.setColumnView(12, COLUMN_WIDTH);
		Label label13 = new Label(13, row, "销售平台", titleFormat);
		ws.addCell(label13);
		ws.setColumnView(13, COLUMN_WIDTH);
		Label label14 = new Label(14, row, "代理的平台名称", titleFormat);
		ws.addCell(label14);
		ws.setColumnView(14, COLUMN_WIDTH);
		Label label15 = new Label(15, row, "价差金额", titleFormat);
		ws.addCell(label15);
		ws.setColumnView(15, COLUMN_WIDTH);
		Label label16 = new Label(16, row, "价差", titleFormat);
		ws.addCell(label16);
		ws.setColumnView(16, COLUMN_WIDTH);
		Label label17 = new Label(17, row, "价差原因", titleFormat);
		ws.addCell(label17);
		ws.setColumnView(17, COLUMN_WIDTH);
		return ws;
	}

	private void formatWriteable() throws WriteException {
		titleFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				10, WritableFont.BOLD);
		titleFont.setColour(Colour.WHITE);
		titleFormat = new jxl.write.WritableCellFormat(titleFont);
		titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
		titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
		titleFormat.setAlignment(Alignment.CENTRE); // 水平对齐
		titleFormat.setWrap(true); // 是否换行
		titleFormat.setBackground(Colour.OCEAN_BLUE);// 背景色暗灰-25%

		normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11);
		normalFormat = new jxl.write.WritableCellFormat(normalFont);
		normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat.setAlignment(Alignment.CENTRE);
		normalFormat.setWrap(false);

		normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11);

		boldFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11, WritableFont.BOLD);
		boldFormat = new jxl.write.WritableCellFormat(boldFont);
		boldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		boldFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		boldFormat.setAlignment(Alignment.CENTRE);
		boldFormat.setWrap(false);

	}

	/**
	 * 获得以当天开始连续10天的日期
	 * 
	 * @return
	 */
/*	private List<String> getDateList() {
		List<String> dateList = new ArrayList<String>();
		if (currentDate == null) {
			currentDate = DateUtils.getDateStr("yyyy-MM-dd");
		}
		dateList.add(currentDate);
		for (int i = 1; i <= COUNT - 1; i++) {
			dateList.add(DateUtils.getDateAfter(i));
		}

		return dateList;
	}*/

	
	
	
	
	
	
	
	
	
	
	
	
	
	@Override
	public Serializable getId() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isNew() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean existed() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean notExisted() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean existed(String propertyName, Object propertyValue) {
		// TODO Auto-generated method stub
		return false;
	}


	public String getEmail() {
		return email;
	}


	public void setEmail(String email) {
		this.email = email;
	}
	
}
